import pandas as pd
import numpy as np

def equal_split(total, n):
    base = round(total / n, 2)
    values = [base] * n
    current_total = round(sum(values), 2)
    diff = round(total - current_total, 2)
    if diff != 0:
        values[-1] = round(values[-1] + diff, 2)
    return values

def ensure_sum_fix(values, target_total):
    values = [round(v, 2) for v in values]
    current_total = round(sum(values), 2)
    diff = round(target_total - current_total, 2)
    if diff != 0:
        values[-1] = round(values[-1] + diff, 2)
    return values

INPUT_PATH = "runs\\20251009-135634\\input.xlsx"

df = pd.read_excel(INPUT_PATH, sheet_name='退货明细', engine='openpyxl')

person_stats = df.groupby('Person').agg(
    Items=('Item', 'count'),
    TotalAmount=('Amount', 'sum')
).reset_index()

total_amount = round(person_stats['TotalAmount'].sum(), 2)
n_persons = len(person_stats)
share_amounts = equal_split(total_amount, n_persons)

person_stats['ShareAmount'] = share_amounts
person_stats['ShareAmount'] = ensure_sum_fix(person_stats['ShareAmount'].tolist(), total_amount)

result_df = person_stats[['Person', 'Items', 'ShareAmount']].copy()
result_df['ShareAmount'] = result_df['ShareAmount'].round(2)

with pd.ExcelWriter(INPUT_PATH, mode='a', if_sheet_exists='replace', engine='openpyxl') as writer:
    result_df.to_excel(writer, sheet_name='Result', index=False)

print("DONE: 每人退货统计与金额分摊完成")